package cn.melon.mod.roll;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.melon.Helper.DBHelper;
import cn.melon.base.IBaseExtractor;
import net.ucanaccess.jdbc.UcanaccessSQLException;

/**
 * IRollDaoImpl.java.
 * 
 * 有关数据库的访问操作, 其中实现了一大部分
 * 
 * 我在此表达一下对Access的不满, 说真的, 你开发一个官方的jdbc驱动会怎么样, 你的Access的有什么值得骄傲的黑科技吗?
 * 
 * @author corvo
 * @date Aug 27, 2016
 */
/**
 * @author corvo
 *
 */
public class IRollDaoImpl implements IRollDao {

    public String sqlCreate = "CREATE TABLE roll" + "(id INT, " + "student_number TEXT(10), " + "name TEXT(20), "
            + "cardNum TEXT(10), " + "sex TEXT(5), " + "nation TEXT(20), " + "political_status TEXT(5), "
            + "marital_status TEXT(5), " + "id_card TEXT(50), " + "date_birth TEXT(20), " + "native_place TEXT(5), "
            + "address TEXT(50), " + "cur_campus TEXT(5), " + "department TEXT(20), " + "major TEXT(10), "
            + "cur_grade TEXT(5), " + "cur_class TEXT(10), " + "length TEXT(5), " + "phone TEXT(20), "
            + "email TEXT(20), " + "sign TEXT(50), " + "updateDate TEXT(10))";

    private DBHelper<RollInfo> dbHelper;

    // 此处定义的内部类提供了转换接口, 对数据库的查询必须实现该接口
    private IBaseExtractor<RollInfo> extractor = new IBaseExtractor<RollInfo>() {

        @Override
        public List<RollInfo> convertToList(ResultSet rs) {

            if (rs == null) {
                return null;
            }

            List<RollInfo> infoList = new ArrayList<RollInfo>();
            try {
                while (rs.next()) {
                    RollInfo info = new RollInfo();

                    info.setStudentNumber(rs.getString(2));
                    info.setName(rs.getString(3));
                    info.setCardNum(rs.getString(4));
                    info.setSex(rs.getString(5));
                    info.setNation(rs.getString(6));
                    info.setPoliticalStatus(rs.getString(7));
                    info.setMaritalStatus(rs.getString(8));
                    info.setIdCard(rs.getString(9));
                    info.setDateBirth(rs.getString(10));
                    info.setNativePlace(rs.getString(11));
                    info.setAddress(rs.getString(12));
                    info.setCurCampus(rs.getString(13));
                    info.setDepartment(rs.getString(14));
                    info.setMajor(rs.getString(15));
                    info.setCurGrade(rs.getString(16));
                    info.setCurClass(rs.getString(17));
                    info.setLength(rs.getString(18));
                    info.setPhone(rs.getString(19));
                    info.setEmail(rs.getString(20));
                    info.setSign(rs.getString(21));
                    info.setUpdateDate(rs.getString(22));

                    infoList.add(info);
                }

                // rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return infoList;
        }
    };

    public IRollDaoImpl() {
        dbHelper = new DBHelper<RollInfo>();
    }

    @Override
    public boolean addInfo(RollInfo info) {
        String insertSql = "INSERT INTO roll "
                + "(student_number, name, cardNum, sex, nation, political_status, marital_status, "
                + "id_card, date_birth, native_place, address, cur_campus, department, "
                + "major, cur_grade, cur_class, length, phone, email, sign, updateDate) " + "VALUES "
                + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        String[] paras = new String[21];
        paras[0] = info.getStudentNumber();
        paras[1] = info.getName();
        paras[2] = info.getCardNum();
        paras[3] = info.getSex();
        paras[4] = info.getNation();
        paras[5] = info.getPoliticalStatus();
        paras[6] = info.getMaritalStatus();
        paras[7] = info.getIdCard();
        paras[8] = info.getDateBirth();
        paras[9] = info.getNativePlace();
        paras[10] = info.getAddress();
        paras[11] = info.getCurCampus();
        paras[12] = info.getDepartment();
        paras[13] = info.getMajor();
        paras[14] = info.getCurGrade();
        paras[15] = info.getCurClass();
        paras[16] = info.getLength();
        paras[17] = info.getPhone();
        paras[18] = info.getEmail();
        paras[19] = info.getSign();
        paras[20] = info.getUpdateDate();

        return dbHelper.sqlUpdate(insertSql, paras);
    }

    @Override
    public boolean deleteInfo(String studentNumber) {
        String sqlDelete = "DELETE FROM roll WHERE student_number = ?";
        return dbHelper.sqlUpdate(sqlDelete, new String[] { studentNumber });
    }

    @Override
    public boolean updateInfo(RollInfo info) {
        String sqlUpdate = "update roll set "
                + "name=?,cardNum=?,sex=?,nation=?,"
                + "political_status=?,marital_status=?,id_card=?,date_birth=?,"
                + "native_place=?,address=?,cur_campus=?,"
                + "department=?,major=?,cur_grade=?,cur_class=?,"
                + "length=?,phone=?,email=?,sign=?,updateDate=? "
                + "where "
                + "student_number=?;";

        String[] paras = { info.getName(), info.getCardNum(), info.getSex(), info.getNation(),
                info.getPoliticalStatus(), info.getMaritalStatus(), info.getIdCard(), info.getDateBirth(),
                info.getNativePlace(), info.getAddress(), info.getCurCampus(), info.getDepartment(), info.getMajor(),
                info.getCurGrade(), info.getCurClass(), info.getLength(), info.getPhone(), info.getEmail(),
                info.getSign(), info.getUpdateDate(), info.getStudentNumber() };
        return dbHelper.sqlUpdate(sqlUpdate, paras);
    }

    @Override
    public RollInfo getInfo(String studentNumber) {
        String sqlQuery = "SELECT * FROM roll where student_number = ?";
        String[] paras = new String[] { studentNumber };

        List<RollInfo> infoList = null;
        try {
            infoList = dbHelper.sqlQuery(sqlQuery, paras, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }

        if (infoList != null && infoList.size() > 0) {
            return infoList.get(0);
        } else {
            return null;
        }

    }

    @Override
    public List<RollInfo> getAllRollInfo() {

        String sqlQuery = "SELECT * FROM roll";

        try {
            return dbHelper.sqlQuery(sqlQuery, new String[] {}, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
